# coding:utf-8
from odoo import models, fields, api, tools


class StockReserveReport(models.Model):
    _name = 'stock.reservation.view'
    _description = "Stock Reservation Report"
    _auto = False

    product_id = fields.Many2one('product.product', string=u"产品")
    lot_ref = fields.Char(string=u"追踪号")
    reserve_qty = fields.Float(string=u"预留量")
    quant_qty = fields.Float(string=u"总库存量")
    remain_qty = fields.Float( string=u"可用量")
    date_validity = fields.Datetime(string=u"到期日")
    user_id = fields.Many2one('res.users', string=u'预留人员')

    order = "product_id, date_validity"

    @api.model_cr
    def init(self):
        tools.drop_view_if_exists(self._cr, 'stock_reservation_view')
        self._cr.execute("""
            CREATE OR REPLACE VIEW stock_reservation_view as
            SELECT row_number() over() as id,
            reserve.product_id, quants.name as lot_ref, quants.quant_qty, reserve.reserve_qty,
            quants.quant_qty - reserve.reserve_qty as remain_qty,
            reserve.user_id, reserve.date_validity
            FROM
            (
                SELECT sm.product_id,sq.lot_id, sum(sm.product_qty) as reserve_qty, sr.user_id, sr.date_validity
                FROM stock_reservation sr
                INNER JOIN stock_move sm ON sr.move_id = sm.id
                LEFT JOIN stock_quant sq ON sr.move_id = sq.reservation_id
                WHERE sr.state2<>'cancel'
                GROUP BY sm.product_id, sq.lot_id, sr.user_id, sr.date_validity
            ) AS reserve
            LEFT JOIN
            (
                SELECT pp.id as product_id, sq.lot_id, spl.name, sum(sq.qty) as quant_qty FROM
                product_product pp
                LEFT JOIN stock_quant sq ON pp.id = sq.product_id
                LEFT JOIN stock_location sl ON sq.location_id = sl.id
                LEFT JOIN stock_production_lot spl ON spl.id = sq.lot_id
--                 WHERE sl.usage IN ('internal', 'procurement', 'production', 'transit')
                GROUP BY pp.id,  sq.lot_id, spl.name
            ) AS quants
            ON quants.product_id = reserve.product_id
            AND COALESCE(quants.lot_id, 0, quants.lot_id) = COALESCE(reserve.lot_id, 0, reserve.lot_id)
        """)

StockReserveReport()
